
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_DimCustomer_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_DimCustomer_Update]
GO


create procedure dbo.p_DimCustomer_Update (
	@CustomerKey int,
	@CustomerIdentifier varchar (30),
	@FullName nvarchar (150),
	@NamePrefix nvarchar (50),
	@GivenName nvarchar (50),
	@FamilyName nvarchar (50),
	@MiddleName nvarchar (50),
	@NameSuffix nvarchar (20),
	@Initials varchar (20),
	@Nickname nvarchar (50),
	@MadienName nvarchar (50),
	@MarriedName nvarchar (50),
	@ProfessionalTtitle nvarchar (150),
	@AcademicTitle nvarchar (150),
	@EmailAddress varchar (256),
	@EmailAddressDomain varchar (150),
	@DateOfBirth datetime,
	@Age int,
	@Gender nvarchar (20),
	@Locality nvarchar (150),
	@PhoneNumber varchar (20),
	@Nationality nvarchar (50),
	@SocialSecurityNumber varchar (30),
	@Relationship nvarchar (30))
as
begin

	-- Disable row counting
	set nocount on

	declare @Error int,
	        @inTranCount int,
	        @Rows int

	select @Error = 0,
	       @inTranCount = @@trancount

	-- Only start our own transaction if caller has not supplied one
	if (@inTranCount = 0) begin transaction txnDimCustomerUpdate
        else
	    save transaction txnDimCustomerUpdate

	set @Error = @@error
	if (@Error <> 0) goto cleanup		

	update dbo.DimCustomer set CustomerIdentifier = @CustomerIdentifier,
				   FullName = @FullName,
				   NamePrefix = @NamePrefix,
				   GivenName = @GivenName,
				   FamilyName = @FamilyName,
				   MiddleName = @MiddleName,
				   NameSuffix = @NameSuffix,
				   Initials = @Initials,
				   Nickname = @Nickname,
				   MadienName = @MadienName,
				   MarriedName = @MarriedName,
				   ProfessionalTitle = @ProfessionalTitle,
				   AcademicTitle = @AcademicTitle,
				   EmailAddress = @EmailAddress,
				   EmailAddressDomain = @EmailAddressDomain,
				   DateOfBirth = @DateOfBirth,
				   Age = @Age,
				   Gender = @Gender,
				   Locality = @Locality,
				   PhoneNumber = @PhoneNumber,
				   Nationality = @Nationality,
				   SocialSecurityNumber = @SocialSecurityNumber,
				   Relationship = @Relationship
	  where (CustomerKey = @CustomerKey)
	

	select @Error = @@error,
	       @Rows = @@rowcount
	if ((@Error = 0) and
	    (@Rows = 0)) set @Error = -2
	if (@Error <> 0) goto cleanup

cleanup:

	-- End transaction
	if (@@trancount > 0)
	begin

		-- On error roll back to save point or local transaction else commit
		if (@Error <> 0) rollback transaction txnDimCustomerUpdate
		else
		if (@inTranCount = 0) commit transaction txnDimCustomerUpdate

	end

	return @Error

end
GO

grant execute on dbo.p_DimCustomer_Update  to [db_datawriter]
go
